// 根据用户id查询已加入的班级信息，并统计每个班级学生与老师的人数
import express from 'express';
import pool from "../importantApi/db_connection.js";

const router = express.Router();
router.get("/", async (req, res) => {
    const {userId} = req.query;
    if (!userId) {
        return res.status(401).json({
            code: 401,
            error: "缺少必要参数"
        });
    }
    try {
        let [result] = await pool.query(`
            SELECT c.class_id,
                   c.class_name,
                   c.user_id                                                           AS manager_id,
                   u.user_name                                                         AS manager_name,
                   COUNT(DISTINCT CASE WHEN u.identity = 'teacher' THEN u.user_id END) AS teacher_count,
                   COUNT(DISTINCT CASE WHEN u.identity = 'student' THEN u.user_id END) AS student_count
            FROM classinfo c
                     JOIN user_class uc ON c.class_id = uc.class_id
                     JOIN userinfo u ON uc.user_id = u.user_id
            WHERE c.class_id IN (SELECT class_id
                                 FROM user_class
                                 WHERE user_id = ${userId})
            GROUP BY c.class_id, c.class_name, c.user_id, c.createtime
            ORDER BY c.createtime DESC
        `);
        return res.status(200).json({
            code: 200,
            data: result
        })
    } catch (err) {
        console.log(err)
        return res.status(500).json({
            code: 500,
            error: err,
            msg: "查询错误"
        })
    }
})


export default router;
